﻿//-------------------------------------------------------------------------------------------------------------------------------------
// Copyright (C) 2005 greatelsoft.com All Rights Reserved
//-------------------------------------------------------------------------------------------------------------------------------------

using System;
using System.Data.Common;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Pub.Class;
using GTS.MiniBox.Entity;

namespace GTS.MiniBox.Helper {
    /// <summary>
    /// 操作类
    /// 
    /// 修改纪录
    ///     2012-06-29 版本：1.0 系统自动创建此类
    /// 
    /// </summary>
    public partial class ChannelRuleHelper {
        /// <summary>
        /// 批量删除指定通道规则
        /// </summary>
        /// <param name="channelID">通道编号</param>
        /// <param name="type">类型 1SMS 2IVR 3WAP</param>
        /// <param name="cityCodeList">区号列表</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时使用ConnString连接</param>
        /// <returns>删除是否成功</returns>
        public static bool DeleteByCityCodeList(int channelID, int type, string cityCodeList, string dbkey = "") {
            string strSql = new SQL().Delete(ChannelRule._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRule._CityCode, "(" + cityCodeList + ")", Operator.In)
                    .And(ChannelRule._ChannelID, channelID, Operator.Equal)
                    .And(ChannelRule._Type, type, Operator.Equal)
                ).ToString();
            int value = dbkey.IsNullEmpty() ? Data.ExecSql(strSql) : Data.Pool(dbkey).ExecSql(strSql);
            if (value != 1) return false;
            return true;
        }
        /// <summary>
        /// 手动更新排行
        /// </summary>
        /// <param name="cstCode">运营商</param>
        /// <param name="cityCode">区号</param>
        /// <param name="order">排行</param>
        /// <param name="dbkey"></param>
        public static bool UpdateOrder(int cstCode, string cityCode, int oldOrder, int order, string dbkey = "") {
            if (oldOrder == order) return false;
            string strSql = new SQL()
                .Update(ChannelRule._)
                .Set("OrderNum=OrderNum{0}1".FormatWith(oldOrder > order ? "+" : "-"))
                .Where("1=1").Where(new Where()
                    .And(ChannelRule._CstCode, cstCode, Operator.Equal)
                    .And(ChannelRule._CityCode, cityCode, Operator.Equal)
                    .And(ChannelRule._OrderNum, oldOrder > order ? order : oldOrder, Operator.MoreThanOrEqual)
                    .And(ChannelRule._OrderNum, oldOrder > order ? oldOrder : order, Operator.LessThanOrEqual)
                ).ToString();
            return Data.Pool(dbkey).ExecSql(strSql) > 0 ? true : false;
        }
        /// <summary>
        /// 批量修改金额比
        /// </summary>
        /// <param name="cstCode">运营商</param>
        /// <param name="cityCode">区号</param>
        /// <param name="channels">通道</param>
        /// <param name="dbkey"></param>
        /// <returns></returns>
        public static bool BatchUpdateByChannelID(int cstCode, string cityCode, string channels, string dbkey = "") {
            string strSql = new SQL()
                .Update(ChannelRule._)
                .Set(ChannelRule._OrderNum, 0)
                .Where("1=1").Where(new Where()
                    .And(ChannelRule._CstCode, cstCode, Operator.Equal)
                    .And(ChannelRule._CityCode, cityCode, Operator.Equal)
                    .And(ChannelRule._ChannelID, "(" + channels + ")", Operator.In)
                ).ToString();
            return Data.Pool(dbkey).ExecSql(strSql) > 0 ? true : false;
        }
        /// <summary>
        /// 查所有分省份、分通道效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetAllPrivenceChannelList(string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetAllPrivenceChannelList";
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                string strSql = new SQL()
                    .Select(ChannelRule._CstCode, CityInfo._ProvinceCode, ChannelRule._ChannelID, ChannelRule._Type)
                    .Select("cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRule._)
                    .InnerJoinNoLock(CityInfo._).On(ChannelRule._CstCode, "=", CityInfo._CityCode).AndOn(ChannelRule._CityCode, "=", CityInfo._CityCode)
                    .GroupBy(ChannelRule._CstCode, CityInfo._ProvinceCode, ChannelRule._ChannelID, ChannelRule._Type)
                    .ToString();
                return Data.Pool(dbkey).GetDbDataReader(strSql).ToList<ChannelRule>();
            });
        }
        /// <summary>
        /// 查所有分通道效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetAllChannelList(string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetAllChannelList";
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                string strSql = new SQL()
                    .Select(ChannelRule._CstCode, ChannelRule._ChannelID, ChannelRule._Type)
                    .Select("cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRule._)
                    .GroupBy(ChannelRule._CstCode, ChannelRule._ChannelID, ChannelRule._Type)
                    .ToString();
                return Data.Pool(dbkey).GetDbDataReader(strSql).ToList<ChannelRule>();
            });
        }
        /// <summary>
        /// 查询所有禁用/手控排序/带量的通道
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetDisableChannelList(string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetDisableChannelList";
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                SQL sql = new SQL()
                    .Select(ChannelRule._CstCode, ChannelRule._CityCode, ChannelRule._ChannelID, ChannelRule._Type)
                    .Select(ChannelRule._SuccessRate, ChannelRule._SendCount, ChannelRule._SendUser, ChannelRule._Ranking)
                    .Select(ChannelRule._SendMoney, ChannelRule._SpCount, ChannelRule._SpUser, ChannelRule._SpMoney)
                    .Select(ChannelRule._StatusResult, ChannelRule._Status)
                    .FromNoLock(ChannelRule._)
                    .Where(ChannelRule._Status, "!=", "0")
                    .OrderByDescending(ChannelRule._StatusDate);
                return Data.Pool(dbkey).GetDbDataReader(sql.ToString()).ToList<ChannelRule>();
            });
        }
        /// <summary>
        /// 查某运营商、开始时间和结束时间内的 所有分省份效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetPrivenceListByDayLog(int cstcode, string begin, string end, string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetPrivenceListByDayLog_{0}_{1}_{2}".FormatWith(cstcode, begin, end);
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                SQL sql = new SQL()
                    .Select(ChannelRuleDay._CstCode, CityInfo._ProvinceCode)
                    .Select("case when SUM(SendMoney) > 0 then cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) else 0 end SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRuleDay._)
                    .InnerJoinNoLock(CityInfo._).On(ChannelRuleDay._CityCode, "=", CityInfo._CityCode)
                    .Where(ChannelRuleDay._CstCode, "=", cstcode.ToString())
                    .GroupBy(ChannelRuleDay._CstCode, ChannelRuleDay._ProvinceCode);
                if (begin.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, ">=", "'{0}'".FormatWith(begin.ToDateTime().DayBegin()));
                if (end.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, "<=", "'{0}'".FormatWith(end.ToDateTime().DayEnd()));
                return Data.Pool(dbkey).GetDbDataReader(sql.ToString()).ToList<ChannelRule>();
            });
        }
        /// <summary>
        /// 查某运营商、某省份、开始时间和结束时间内的 所有分省份、分通道效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetPrivenceChannelListByDayLog(int cstcode, int privence, string begin, string end, string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetPrivenceChannelListByDayLog_{0}_{1}_{2}_{3}".FormatWith(cstcode, begin, end, privence);
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                SQL sql = new SQL()
                    .Select(ChannelRuleDay._CstCode, CityInfo._ProvinceCode, ChannelRuleDay._ChannelID, ChannelRuleDay._Type)
                    .Select("cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRuleDay._)
                    .InnerJoinNoLock(CityInfo._).On(ChannelRuleDay._CityCode, "=", CityInfo._CityCode)
                    .Where(ChannelRuleDay._CstCode, "=", cstcode.ToString())
                    .AndWhere(CityInfo._ProvinceCode, "=", privence.ToString())
                    .GroupBy(ChannelRuleDay._CstCode, ChannelRuleDay._ProvinceCode, ChannelRuleDay._ChannelID, ChannelRuleDay._Type);
                if (begin.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, ">=", "'{0}'".FormatWith(begin.ToDateTime().DayBegin()));
                if (end.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, "<=", "'{0}'".FormatWith(end.ToDateTime().DayEnd()));
                return Data.Pool(dbkey).GetDbDataReader(sql.ToString()).ToList<ChannelRule>();
            });
        }
        /// <summary>
        /// 查某运营商、某区域、开始时间和结束时间内的 所有分通道效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetAreaChannelListByDayLog(int cstcode, string area, string begin, string end, string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetAreaChannelListByDayLog_{0}_{1}_{2}_{3}".FormatWith(cstcode, area, begin, end);
            SQL sql = new SQL()
                    .Select(ChannelRuleDay._CstCode, ChannelRuleDay._ChannelID, ChannelRuleDay._Type)
                    .Select("case when SUM(SendMoney) > 0 then cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) else 0 end SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRuleDay._);

            sql.Where(ChannelRuleDay._CstCode, "=", cstcode.ToString());
            if (area.Length == 4) sql.AndWhere(ChannelRuleDay._CityCode, "=", area.SafeSql());
            if (!area.IsNullEmpty() && area.Length < 4) {
                string areas = CityInfoHelper.SelectListByAll(dbkey).Where(p => p.ProvinceCode == area.ToInt(0)).Select(p => p.CityCode).Join<string>(",");
                if (!areas.IsNullEmpty()) sql.AndWhere(ChannelRuleDay._CityCode, " in ", "({0})".FormatWith(areas));
            }
            if (begin.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, ">=", "'{0}'".FormatWith(begin.ToDateTime().DayBegin()));
            if (end.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, "<=", "'{0}'".FormatWith(end.ToDateTime().DayEnd()));
            sql.GroupBy(ChannelRuleDay._CstCode, ChannelRuleDay._ChannelID, ChannelRuleDay._Type);
            return Data.Pool(dbkey).GetDbDataReader(sql.ToString()).ToList<ChannelRule>();
        }
        /// <summary>
        /// 查某运营商、某通道、开始时间和结束时间内的 所有分区域效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetChannelAreaListByDayLog(int cstcode, int channelID, int type, string begin, string end, string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetChannelAreaListByDayLog_{0}_{1}_{2}_{3}".FormatWith(cstcode, channelID, begin, end);
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                SQL sql = new SQL()
                    .Select(ChannelRuleDay._CityCode)
                    .Select("cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRuleDay._);
                sql.Where(ChannelRuleDay._ChannelID, "=", channelID.ToString());
                if (cstcode > -1) sql.AndWhere(ChannelRuleDay._CstCode, "=", cstcode.ToString());
                if (begin.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, ">=", "'{0}'".FormatWith(begin.ToDateTime().DayBegin()));
                if (end.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, "<=", "'{0}'".FormatWith(end.ToDateTime().DayEnd()));
                sql.AndWhere(ChannelRuleDay._Type, "=", "'{0}'".FormatWith(type));
                sql.GroupBy(ChannelRuleDay._CityCode);
                return Data.Pool(dbkey).GetDbDataReader(sql.ToString()).ToList<ChannelRule>();
            });
        }
        /// <summary>
        /// 查某运营商、某省份、开始时间和结束时间内的 所有分运营商、分区域、分通道效果
        /// </summary>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回实体记录集</returns>
        public static IList<ChannelRule> GetPrivenceAreaChannelListByDayLog(int cstcode, int privence, string begin, string end, string dbkey = "") {
            string cacheNameKey = "GTS.MiniBox.ChannelRuleCache_GetPrivenceAreaChannelListByDayLog_{0}_{1}_{2}_{3}".FormatWith(cstcode, begin, end, privence);
            return Cache2.Get<IList<ChannelRule>>(cacheNameKey, cacheSeconds, () => {
                SQL sql = new SQL()
                    .Select(ChannelRuleDay._CstCode, ChannelRuleDay._CityCode, ChannelRuleDay._ChannelID, ChannelRuleDay._Type)
                    .Select("case when SUM(SendMoney) > 0 then cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) else 0 end SuccessRate", "SUM(SendCount) SendCount", "SUM(SendUser) SendUser", "AVG(Ranking) Ranking")
                    .Select("SUM(SendMoney) SendMoney", "SUM(SpCount) SpCount", "SUM(SpUser) SpUser", "SUM(SpMoney) SpMoney")
                    .FromNoLock(ChannelRuleDay._)
                    .InnerJoinNoLock(CityInfo._).On(ChannelRuleDay._CityCode, "=", CityInfo._CityCode)
                    .Where(ChannelRuleDay._CstCode, "=", cstcode.ToString())
                    .AndWhere(CityInfo._ProvinceCode, "=", privence.ToString())
                    .GroupBy(ChannelRuleDay._CstCode, ChannelRuleDay._CityCode, ChannelRuleDay._ChannelID, ChannelRuleDay._Type);
                if (begin.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, ">=", "'{0}'".FormatWith(begin.ToDateTime().DayBegin()));
                if (end.IsDate()) sql.AndWhere(ChannelRuleDay._UpdateTime, "<=", "'{0}'".FormatWith(end.ToDateTime().DayEnd()));
                return Data.Pool(dbkey).GetDbDataReader(sql.ToString()).ToList<ChannelRule>();
            });
        }
    }
}

